﻿using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using PubOp;
using System.Collections;
using System.Drawing;

namespace Statistics
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        static string strFilePath = Application.StartupPath + @"\config.ini";
        OperateIniFile op = new OperateIniFile();
        static string servers = OperateIniFile.ReadIniData("数据库连接", "server", "", strFilePath);
        static string datas = OperateIniFile.ReadIniData("数据库连接", "database", "", strFilePath);
        static string names = OperateIniFile.ReadIniData("数据库连接", "uname", "", strFilePath);
        static string pwds = OperateIniFile.ReadIniData("数据库连接", "pwd", "", strFilePath);
        static string sqlcon = "Data Source=" + servers + ";Initial Catalog=" + datas + ";User ID=" + names + ";pwd=" + pwds + ";";
        SqlConnection conn = new SqlConnection(sqlcon);
        //绑定车辆类型
        public void QuerySetCLLX()
        {
            conn.Open();
            string str = string.Format("select ROW_NUMBER() OVER(ORDER BY 车辆类型) ID,车辆类型 from DM_vRegister_固定信息 group by 车辆类型 having  车辆类型 != ''");
            SqlDataAdapter sda = new SqlDataAdapter(str, conn);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            cllx.Columns.Clear();  //先清除再添加保证数据的一致性   
            cllx.Columns.Add("ID");
            cllx.Columns.Add("车辆类型");
            cllx.Items.Clear();
            
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                ListViewItem lvi = new ListViewItem();
                lvi.SubItems.Add(dt.Rows[i][1].ToString());
                this.cllx.Items.Add(lvi);
            }
            conn.Close();
            cllx.Columns[0].Width = 30;
            cllx.Columns[1].Width = 172;
        }
        //绑定检测项目
        public void QuerySetJCXM()
        {
            conn.Open();
            string str = string.Format("select ROW_NUMBER() OVER(ORDER BY 项目名) ID,项目名 from DM_hTestItems_项目列表 where 项目名!='过车'");
            SqlDataAdapter sda = new SqlDataAdapter(str, conn);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            jcxm.Columns.Clear();  //先清除再添加保证数据的一致性   
            jcxm.Columns.Add("ID");
            jcxm.Columns.Add("项目名");
            jcxm.Items.Clear();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                ListViewItem lvi = new ListViewItem();
                lvi.SubItems.Add(dt.Rows[i][1].ToString());
                this.jcxm.Items.Add(lvi);
            }
            conn.Close();
            jcxm.Columns[0].Width = 30;
            jcxm.Columns[1].Width = 172;
        }
        //绑定检测线号
        public void QuerySetJCXH()
        {
            conn.Open();
            string str = string.Format("select ROW_NUMBER() OVER(ORDER BY 线号标识) ID,线号标识 from DM_vRegister_变化信息 group by 线号标识");
            SqlDataAdapter sda = new SqlDataAdapter(str, conn);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            jcxh.Columns.Clear();  //先清除再添加保证数据的一致性   
            jcxh.Columns.Add("ID");
            jcxh.Columns.Add("线号标识");
            jcxh.Items.Clear();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                ListViewItem lvi = new ListViewItem();
                lvi.SubItems.Add(dt.Rows[i][1].ToString());
                this.jcxh.Items.Add(lvi);
            }
            conn.Close();
            jcxh.Columns[0].Width = 30;
            jcxh.Columns[1].Width = 170;
        }
        //绑定检测类别
        public void QuerySetJCLB()
        {
            conn.Open();
            string str = string.Format("select ROW_NUMBER() OVER(ORDER BY 检测类别) ID,检测类别 from DM_vRegister_变化信息 group by 检测类别");
            SqlDataAdapter sda = new SqlDataAdapter(str, conn);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            jclb.Columns.Clear();  //先清除再添加保证数据的一致性   
            jclb.Columns.Add("ID");
            jclb.Columns.Add("检测类别");
            jclb.Items.Clear();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                ListViewItem lvi = new ListViewItem();
                lvi.SubItems.Add(dt.Rows[i][1].ToString());
                this.jclb.Items.Add(lvi);
            }
            conn.Close();
            jclb.Columns[0].Width = 30;
            jclb.Columns[1].Width = 170;
        }
        //加载
        private void Form1_Load(object sender, EventArgs e)
        {
            QuerySetCLLX();
            QuerySetJCXM();
            QuerySetJCXH();
            QuerySetJCLB();
            dataGridView1.RowsDefaultCellStyle.Font = new System.Drawing.Font("宋体", 9); 
            dataGridView1.RowsDefaultCellStyle.BackColor = Color.Red;
        }
        //查询
        private void button1_Click(object sender, EventArgs e)
        {
            //车辆类型
            ArrayList alcllx = new ArrayList();
            for (int i=0;i<cllx.Items.Count;i++)
            {
                if(cllx.Items[i].Checked)//
                {
                    alcllx.Add(cllx.Items[i].SubItems[1].Text);
                }
            }
            string strcllx = string.Join(",", (string[])alcllx.ToArray(typeof(string)));
            //检测项目
            ArrayList aljcxm = new ArrayList();
            for (int i = 0; i < jcxm.Items.Count; i++)
            {
                if (jcxm.Items[i].Checked)//
                {
                    aljcxm.Add(jcxm.Items[i].SubItems[1].Text);
                }
            }
            string strjcxm = string.Join(",", (string[])aljcxm.ToArray(typeof(string)));
            //检测线号
            ArrayList aljcxh = new ArrayList();
            for (int i = 0; i < jcxh.Items.Count; i++)
            {
                if (jcxh.Items[i].Checked)
                {
                    aljcxh.Add(jcxh.Items[i].SubItems[1].Text);
                }
            }
            string strjcxh = string.Join(",", (string[])aljcxh.ToArray(typeof(string)));
            //检测类别
            ArrayList aljclb = new ArrayList();
            for (int i = 0; i < jclb.Items.Count; i++)
            {
                if (jclb.Items[i].Checked)
                {
                    aljclb.Add(jclb.Items[i].SubItems[1].Text);
                }
            }
            string strjclb = string.Join(",", (string[])aljclb.ToArray(typeof(string)));
            conn.Open();
            SqlParameter[] pars = new SqlParameter[]
               {
                 new SqlParameter("@开始日期",dateTimePicker1.Value.ToString("yyyy-MM-dd")),
                 new SqlParameter("@结束日期",dateTimePicker2.Value.ToString("yyyy-MM-dd")),
                 new SqlParameter("@车辆类型",strcllx),
                 new SqlParameter("@检测项目",strjcxm),
                 new SqlParameter("@检测线号",strjcxh),
                 new SqlParameter("@检测类别",strjclb)
               };
            SqlDataAdapter sda = new SqlDataAdapter();
            DataTable dt = new DataTable();
            dt=DBHelper.ExecuteQuery("QueryMationTotal", pars, CommandType.StoredProcedure);
            conn.Close();
            dataGridView1.DataSource = dt;
        }
        //导出
        private void button2_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            // 列强制转换
            for (int count = 0; count < dataGridView1.Columns.Count; count++)
            {
                DataColumn dc = new DataColumn(dataGridView1.Columns[count].Name.ToString());
                dt.Columns.Add(dc);
            }
            // 循环行
            for (int count = 0; count < dataGridView1.Rows.Count; count++)
            {
                DataRow dr = dt.NewRow();
                for (int countsub = 0; countsub < dataGridView1.Columns.Count; countsub++)
                {
                    dr[countsub] = Convert.ToString(dataGridView1.Rows[count].Cells[countsub].Value);
                }
                dt.Rows.Add(dr);
            }
            //DataTable dt = (dataGridView1.DataSource as DataTable);//数据绑定过的数据转换成datable
            if (dt == null) return;
            System.Windows.Forms.SaveFileDialog saveFileDlg = new System.Windows.Forms.SaveFileDialog();
            saveFileDlg.Title = "导出过程数据";
            saveFileDlg.Filter = "过程数据(*.xls)|*.xls";
            saveFileDlg.RestoreDirectory = true;
            if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                string execltext = "日志文件";
                ExcelHelper hp = new ExcelHelper(saveFileDlg.FileName);
                int rst = hp.DataTableToExcel(dt, execltext, true);
                if (rst == -1)
                {
                    MessageBox.Show("导出失败");
                }
                else
                {
                    MessageBox.Show("导出成功");
                }
            }
        }      
        //关闭进程
        private void Form1_FormClosed(object sender, FormClosedEventArgs e)
        {
            Dispose();
            Application.Exit();
        }
        //全选与不选
        public void ReturnText(string type,Button btn,ListView lv)
        {
            if (type == "全不选")
            {
                for (int i = 0; i < lv.Items.Count; i++)
                {
                    lv.Items[i].Checked = true;
                }
                btn.Text = "全选";
            }
            else
            {
                for (int i = 0; i < lv.Items.Count; i++)
                {
                    lv.Items[i].Checked = false;
                }
                btn.Text = "全不选";
            }
        }
        private void button3_Click(object sender, EventArgs e)
        {
            ReturnText(button3.Text, button3,cllx);
        }

        private void button4_Click(object sender, EventArgs e)
        {
            ReturnText(button4.Text, button4,jcxm);
        }

        private void button5_Click(object sender, EventArgs e)
        {
            ReturnText(button5.Text, button5,jcxh);
        }

        private void button6_Click(object sender, EventArgs e)
        {
            ReturnText(button6.Text, button6, jclb);
        }
    }
}
